package cn.saltybe.analysis.service;

import cn.saltybe.analysis.entity.data.RentingHouse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class RentingHouseServiceImpl implements RentingHouseService {
    private JdbcTemplate jdbcTemplate;

    // set方法注入JdbcTemplate
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception {
        this.jdbcTemplate = jdbcTemplate;
        this.createTable();
    }

    private final BeanPropertyRowMapper<RentingHouse> rowMapper = new BeanPropertyRowMapper<RentingHouse>(RentingHouse.class);

    @Override
    public void createTable() throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "CREATE TABLE IF NOT EXISTS `renting_house` (" +
                "`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT," +
                "`type_id` CHAR(32) NOT NULL UNIQUE," +
                "`storage_time` DATETIME NOT NULL," +
                "`update_time` DATETIME NOT NULL," +
                "`city` VARCHAR(8) NOT NULL," +
                "`district` VARCHAR(8) NOT NULL," +
                "`source` TEXT NOT NULL," +
                "`title` TEXT NOT NULL," +
                "`image` TEXT," +
                "`address` TEXT," +
                "`rent_type` CHAR(5)," + // whole | share
                "`price` INT NOT NULL," +
                "`square` DOUBLE NOT NULL" +
                ") ENGINE = InnoDB CHARSET = utf8mb4";
        this.jdbcTemplate.execute(sql);
    }

    @Override
    public int insert(RentingHouse rentingHouse) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "INSERT INTO renting_house (`type_id`, `storage_time`, `update_time`, `city`, `district`, `source`, `title`, `image`, `address`, `rent_type`, `price`, `square`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        this.jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, rentingHouse.getType_id());
                ps.setTimestamp(2, new Timestamp(new Date().getTime()));
                ps.setTimestamp(3, new Timestamp(new Date().getTime()));
                ps.setString(4, rentingHouse.getCity());
                ps.setString(5, rentingHouse.getDistrict());
                ps.setString(6, rentingHouse.getSource());
                ps.setString(7, rentingHouse.getTitle());
                ps.setString(8, rentingHouse.getImage());
                ps.setString(9, rentingHouse.getAddress());
                ps.setString(10, rentingHouse.getRent_type());
                ps.setInt(11, rentingHouse.getPrice());
                ps.setDouble(12, rentingHouse.getSquare());
                return ps;
            }
        }, keyHolder);
        this.jdbcTemplate.getDataSource().getConnection().close(); // 关闭连接
        return keyHolder.getKey().intValue();
    }

    @Override
    public int delete(int id) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "DELETE FROM renting_house WHERE id = ?";
        Object[] params = new Object[] { id };
        return this.jdbcTemplate.update(sql, params);
    }

    @Override
    public int update(RentingHouse rentingHouse) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "UPDATE renting_house SET `update_time` = ?, `title` = ?, `image` = ?, `rent_type` = ?, `price` = ?, `square` = ? WHERE `id` = ?";
        Object[] params = new Object[] { new Timestamp(new Date().getTime()), rentingHouse.getTitle(), rentingHouse.getImage(), rentingHouse.getRent_type(), rentingHouse.getPrice(), rentingHouse.getSquare(), rentingHouse.getId() };
        return this.jdbcTemplate.update(sql, params);
    }

    @Override
    public List<RentingHouse> all() throws Exception {
        return this.all(0, 10);
    }

    @Override
    public List<RentingHouse> all(int offset) throws Exception {
        return this.all(offset, 10);
    }

    /**
     *  如果offset设置为小于0的的值，自动改为0；
     *  如果limit设置为非正数，则查询全列表。
     *
     * @param offset
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public List<RentingHouse> all(int offset, int limit) throws Exception {
        if (offset < 0) offset = 0;
        if (limit < 0) limit = 10;
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        Object[] params = new Object[] { offset, limit };
        final String sql = "SELECT * FROM renting_house LIMIT ?, ?";
        List<RentingHouse> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public RentingHouse get(String typeId) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM renting_house WHERE type_id = ?";
        Object[] params = new Object[] { typeId };
        List<RentingHouse> list = this.jdbcTemplate.query(sql, rowMapper, params);
        if (list.size() == 1) return list.get(0);
        throw new Exception("ERR_ITEM_NOT_EXISTS");
    }

    @Override
    public RentingHouse get(int id) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM renting_house WHERE id = ?";
        Object[] params = new Object[] { id };
        List<RentingHouse> list = this.jdbcTemplate.query(sql, rowMapper, params);
        if (list.size() == 1) return list.get(0);
        throw new Exception("ERR_ITEM_NOT_EXISTS");
    }

    @Override
    public List<RentingHouse> get(int[] id) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        if (id.length == 0) return new ArrayList<RentingHouse>();
        String conditions = "";
        Object[] params = new Object[id.length];
        for (int i = 0; i < id.length; i++) {
            conditions += "id = ?";
            params[i] = id[i];
            if (i + 1 != id.length) conditions += " OR ";
        }
        final String sql = "SELECT * FROM renting_house WHERE " + conditions;
        List<RentingHouse> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public boolean has(String typeId) {
        try {
            this.get(typeId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public boolean has(int id) {
        try {
            this.get(id);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public List<RentingHouse> query(String search, int offset, int limit) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        if (search.length() == 0) return this.all(offset, limit);
        String[] keys = search.split("\\s+");
        Object[] params = new Object[keys.length * 2 + 2];
        String conditions = " WHERE ";
        for (int i = 0; i < keys.length; i++) {
            conditions += "(`title` LIKE ? OR `address` LIKE ?)";
            params[i * 2] = "%" + keys[i] + "%";
            params[i * 2 + 1] = "%" + keys[i] + "%";
            if (i + 1 != keys.length) conditions += " AND ";
        }
        params[params.length - 2] = offset;
        params[params.length - 1] = limit;
        final String sql = "SELECT * FROM renting_house" + conditions + " LIMIT ?, ?";
        List<RentingHouse> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public int count(String search) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        String[] keys = search.split("\\s+");
        Object[] params = new Object[keys.length * 2];
        String conditions = keys.length > 0 ? " WHERE " : "";
        for (int i = 0; i < keys.length; i++) {
            conditions += "(`title` LIKE ? OR `address` LIKE ?)";
            params[i * 2] = "%" + keys[i] + "%";
            params[i * 2 + 1] = "%" + keys[i] + "%";
            if (i + 1 != keys.length) conditions += " AND ";
        }
        final String sql = "SELECT COUNT(id) FROM `renting_house`" + conditions;
        List<Integer> result = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt(1);
            }
        }, params);
        if (result.size() == 1) return result.get(0);
        throw new Exception("ERR_DATABASE_CORRUPTED");
    }
}
